package cc.wenshixin.dao;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.criterion.DetachedCriteria;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.Restrictions;
import org.hibernate.transform.Transformers;
import org.springframework.jdbc.object.SqlQuery;
import org.springframework.orm.hibernate5.HibernateTemplate;
import org.springframework.orm.hibernate5.support.HibernateDaoSupport;

import cc.wenshixin.entity.Customer;
import cc.wenshixin.entity.Level;

public class CustomerDaoImpl extends BaseDaoImpl<Customer> implements CustomerDao {

/*	@Override
	public void add(Customer customer) {
		HibernateTemplate hibernateTemplate = this.getHibernateTemplate();
		hibernateTemplate.save(customer);
	}*/

/*	@SuppressWarnings("all")
	@Override
	public List<Customer> findAll() {
		HibernateTemplate hibernateTemplate = this.getHibernateTemplate();
		List<Customer> customerList =  (List<Customer>) hibernateTemplate.find("FROM Customer ORDER BY id DESC");
		
		if(customerList != null && customerList.size() > 0) {			
			return customerList;
		}
		return null;
	}*/

/*	@Override
	public Customer findOne(int id) {
		HibernateTemplate hibernateTemplate = this.getHibernateTemplate();
		Customer customer = (Customer) hibernateTemplate.get(Customer.class, id);
		return customer;

	}*/

/*	@Override
	public void update(Customer customer) {
		HibernateTemplate hibernateTemplate = this.getHibernateTemplate();
		hibernateTemplate.update(customer);
	}*/

/*	@Override
	public void delete(Customer c) {
		HibernateTemplate hibernateTemplate = this.getHibernateTemplate();
		hibernateTemplate.delete(c);
	}*/

	@SuppressWarnings("all")
	@Override
	public List<Customer> findCondition(Customer customer) {
		HibernateTemplate hibernateTemplate = this.getHibernateTemplate();
		DetachedCriteria criteria = DetachedCriteria.forClass(Customer.class);
		criteria.add(Restrictions.like("name", "%"+customer.getName()+"%"));
		List<Customer> list = (List<Customer>) hibernateTemplate.findByCriteria(criteria);
		return list;
	}

	@SuppressWarnings("all")
	@Override
	public List<Customer> findPage(int begin, int pageSize) {
		HibernateTemplate hibernateTemplate = this.getHibernateTemplate();
		DetachedCriteria criteria = DetachedCriteria.forClass(Customer.class);
		criteria.addOrder(Order.desc("id"));
		List<Customer> list = (List<Customer>) hibernateTemplate.findByCriteria(criteria,begin,pageSize);
		return list;
	}

	@SuppressWarnings("all")
	@Override
	public int findCount() {
		HibernateTemplate hibernateTemplate = this.getHibernateTemplate();
		List<Object> list = (List<Object>) hibernateTemplate.find("SELECT COUNT(id) FROM Customer");
		if(list != null && list.size() > 0) {
			Object object = list.get(0);
			Long lobj = (Long) object;
			int count = lobj.intValue();
			return count;
		}
		return 0;
	}

	@SuppressWarnings("all")
	@Override
	public List<Customer> selectMoreCondition(Customer customer) {
		HibernateTemplate hibernateTemplate = this.getHibernateTemplate();
		/*String hql = "FROM Customer WHERE 1=1 ";
		List<Object> pList = new ArrayList<Object>();
		//第一种方法，拼接字符串
		if(customer.getName() != null && !"".equals(customer.getName())) {
			hql += "AND name LIKE ? ";
			pList.add(customer.getName());
		}
		if(customer.getLevel() != null && !"".equals(customer.getLevel())) {
			hql += "AND level LIKE ? ";
			pList.add(customer.getLevel());
		}
		if(customer.getSource() != null && !"".equals(customer.getSource())) {
			hql += "AND source LIKE ?";
			pList.add(customer.getSource());
		}
		System.out.println(hql);
		List<Customer> list = (List<Customer>) hibernateTemplate.find(hql, pList.toArray());*/
		//第二种方法，离线对象函数调用
		DetachedCriteria criteria = DetachedCriteria.forClass(Customer.class);
		if(customer.getName() != null && !"".equals(customer.getName())) {
			criteria.add(Restrictions.like("name", "%"+customer.getName()+"%"));
		}
		if(customer.getLevel() != null && !"".equals(customer.getLevel())) {
			criteria.add(Restrictions.like("level", "%"+customer.getLevel()+"%"));
		}
		if(customer.getSource() != null && !"".equals(customer.getSource())) {
			criteria.add(Restrictions.like("source", "%"+customer.getSource()+"%"));
		}
		List<Customer> list = (List<Customer>) hibernateTemplate.findByCriteria(criteria);
		return list;
	}

	@SuppressWarnings("all")
	@Override
	public List<Level> findAllLevel() {
		HibernateTemplate hibernateTemplate = this.getHibernateTemplate();
		List<Level> list = (List<Level>) hibernateTemplate.find("FROM Level");
		return list;
	}

	@SuppressWarnings("all")
	@Override
	public List findCountLevel() {
		String sql = "SELECT l.name,c.num FROM (SELECT level,COUNT(level) AS num FROM customer GROUP BY level) AS c, level AS l WHERE l.id = c.level";
		//获取session对象
		Session session = this.getSessionFactory().getCurrentSession();
		//创建sqlQuery对象
		SQLQuery sqlQuery = session.createSQLQuery(sql);
		//返回查询结果，每个元素转换成map集合
		sqlQuery.setResultTransformer(Transformers.aliasToBean(HashMap.class));
		//返回list集合
		List list = sqlQuery.list();
		return list;
	}

	@SuppressWarnings("all")
	@Override
	public List findCountSource() {
		String sql = "SELECT source, COUNT(source) AS num FROM customer GROUP BY source";
		Session session = this.getSessionFactory().getCurrentSession();
		SQLQuery sqlQuery = session.createSQLQuery(sql);
		sqlQuery.setResultTransformer(Transformers.aliasToBean(HashMap.class));
		List list = sqlQuery.list();
		return list;
	}
	
}
